Migration using Oracle as the database

This topic is related to sections:

• The Data Migration Process

• Migration using SAP HANA as the database

• Migration using a second SQL Server as the database

About Using Oracle as the database

When the project uses Oracle as the migration database, the transformation of that data takes place within the Oracle database. Migration remains stored within SQL Server as well as the Construct tables. These notes apply to using Oracle as the migration database:

• If using a version of Oracle less than 19C, Tables and Views must have names with 30 or less characters. If the Table or View name exceeds 30 Characters, the scripting fails to generate in Oracle DB. Refer to section Migrate > Snapshot Management > Edit Snapshot Tables and Columns  for details of ensuring the Table and Column names are 30 Chars or less.

• Each Database contains a Schema of same name (ex:TGTECC.TGTECC.Tablename).

• Users are granted access for each Database, and so GRANT ACCESS is a step in many jobs.

• Every Oracle Datasource must have a corresponding Syniti Knowledge PlatformSystem Connection with the correct USERNAME/PW for each of the schemas on the working database.

• All naming (Tables, Views, Database, Schema, or Fields) defaults to CAPS.

• Use of NULL in Oracle is scripted as NVL -  WHERE NVL(T.ADDRNUMBER,'' '') = '' ''' 

Preparation for using Oracle Database

The Admin sets up the Oracle DB server as a Migration Working Database Server within Administer > Setup > Guided Setup within the initial weeks on site. Refer to section Initial Setup - First Week for details of the initial system setup.

Note

The database version of the Oracle Server must be entered in same case as the datastore for Oracle within Data Services. Data Services is case sensitive and wrong case structure produces issues for importing data for a datasource.

SRCCONSTRUCT Build in Oracle

Create CONSTRUCT Datasource as a Snapshot Source Datasource (SRCCONSTRUCT) within Oracle DB. This new Snapshot datasource stores the CONSTRUCT table data for use during Mock runs as the table data remain static. Each Construct table built within Syniti Construct and stored within SQL Server CONSTRUCT database is added to the SRCCONSTRUCT datasource within Snapshot Management for Oracle DB and the Metadata is imported as well.

Note

Along with storing all Construct pages as tables within the SRCCONSTRUCT database in Oracle, the tables XTVALUEMAP and LIST_TARGET_VALUE are stored in SRCCONSTRUCT db as Snapshot Datasource tables to provide access for Value Mapping work. XREF ETL Logic uses the values in the snapshot table of XTVALUEMAP for translations. The ETL tasks INSERT into MIGRATE.dbo.XTVALUEMAP and update target values from SRCCONSTRUCT.dbo.XTVALUEMAP. The LIST_TARGET_VALUE table provides the Target Value, Load Value, and Description of each record by Datasource and Check Table.

This Source Snapshot is connected to the CONSTRUCT datasource that comes delivered and points to the Syniti Migrate system CONSTRUCT database in SQL Server. As each table is added to the Source Snapshot datasource, the system attempts to import the Metadata (Columns, etc.)

An example of the scripting code for Oracle CREATE Table is listed below:

BEGIN EXECUTE IMMEDIATE ' CREATE TABLE SRCCONSTRUCT.ADDR_CLNS( "ZDATASOURCE" NVARCHAR2(50), "ZDEPLOY" NVARCHAR2(10), "ZLEGACYADDRNUMBER" NVARCHAR2(50), "ZLEGACYDATE_FROM" NVARCHAR2(50), "ZLEGACYNATION" NVARCHAR2(50), "ZADRC_TYPE" NVARCHAR2(10), "ZTITLE" NVARCHAR2(4), "ZNAME1" NVARCHAR2(40), "ZNAME2" NVARCHAR2(40), "ZNAME3" NVARCHAR2(40), "ZNAME4" NVARCHAR2(40), "ZSORT1" NVARCHAR2(20), "ZSORT2" NVARCHAR2(20), "ZSTREET" NVARCHAR2(60), "ZHOUSE_NUM1" NVARCHAR2(10), "ZCITY1" NVARCHAR2(40), "ZREGION" NVARCHAR2(3), "ZPOST_CODE1" NVARCHAR2(10), "ZTEL_EXTENS" NVARCHAR2(10), "ZFAX_NUMBER" NVARCHAR2(30), "ZFAX_EXTENS" NVARCHAR2(10), CREATED_BY NVARCHAR2(50), CREATED_ON DATE, MODIFIED_BY NVARCHAR2(100), MODIFIED_ON DATE, LOCKED_BY NVARCHAR2(50), LOCKED_ON DATE, CONSTRAINT PK_ADDR_CLNS PRIMARY KEY ( "ZSOURCE", "ZDEPLOY", "ZLEGACYADDRNUMBER", "ZLEGACYDATE_FROM", "ZLEGACYNATION", "ZADRC_TYPE"))'; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -955 THEN NULL; ELSE RAISE; END IF; END;

Note

Field names with Upper and Lower characters must include double quotes around them. Methodology suggests only caps.

Report Schema in Oracle DB

The Admin updates the delivered Datasource for REPORT (Stored within SYNITI MIGRATION Application and Instance) to point to the Oracle (working) database Server and Schema.

The REPORT Datasource is stored within Oracle DB. As the object is run through the ETL process, the reports generate and the data is stored in tables within REPORT Schema on Oracle DB. The Parameters page designates the appropriate datasource for reports.

Note

Access to Oracle database using DBeaver software.

Parameters

Access the Parameters page with pathway:  Administer > Setup > Parameters from the Syniti Migrate Home page. The Parameters are set for Oracle DB using the Working DB Type of Oracle that is delivered with the system. Refer to section Administer > Setup > Guided Setup for details of the remaining fields.

Environments

Access the Environments page with pathway:  Administer > Setup > Environments from the Syniti Migrate Home page. Setup the Report Cache Database by a click on Edit  icon for the Environment that is set to Oracle database.

From within the details of the environment, select from the drop down list the Oracle REPORT cache datasource. This datasource is previously created within the Syniti Migrate application within Datasources. Refer to section Catalog > Datasources for more information on the setup of a Report Cache Datasource.

Datasources in Oracle DB

The Admin creates the Application and Instance for Oracle DB within Catalog > Datasources to point to this database system. Each schema is setup as individual connections in the Syniti Data Catalog system. Each schema will have its own password for access.

Once the Snapshot Datasource is built within Syniti Migrate and connected to the System Source or System Target, the user may BUILD XML for the IMPORT process. The Snapshot Datasource tables will automatically build within Oracle DB as part of this process.

Once the Snapshot Datasource is built within Syniti Migrate and connected to the System Datasource, the user may BUILD XML for the IMPORT process. This scenario is active when the ETL Tool = SAP Data Services within the Parameters page.

Note

Should the user attempt to create the Oracle Snapshot Datasource tables manually after they are created, the job queue will indicate a failure due to the tables already existing in the system.

Snapshot Management: Snapshot Datasource: Details

The tables are automatically built within Oracle DB as shown below. If the tables already exist within the snapshot database, the Debug Log is empty - the table already exists and is not rebuilt.

This is an example of the scripting for building tables within Oracle DB:

BEGIN EXECUTE IMMEDIATE ' CREATE TABLE TGTECC.ADR3( CLIENT NVARCHAR2(3), ADDRNUMBER NVARCHAR2(10), PERSNUMBER NVARCHAR2(10), DATE_FROM DATE, CONSNUMBER NVARCHAR2(3), COUNTRY NVARCHAR2(3), FLGDEFAULT NVARCHAR2(1), FLG_NOUSE NVARCHAR2(1), HOME_FLAG NVARCHAR2(1), FAX_NUMBER NVARCHAR2(30), FAX_EXTENS NVARCHAR2(10), FAXNR_LONG NVARCHAR2(30), FAXNR_CALL NVARCHAR2(30), FAX_GROUP NVARCHAR2(1), DFT_RECEIV NVARCHAR2(1), R3_USER NVARCHAR2(1), VALID_FROM NVARCHAR2(14), VALID_TO NVARCHAR2(14), CONSTRAINT PK_ADR3 PRIMARY KEY ( ADDRNUMBER, PERSNUMBER, DATE_FROM, CONSNUMBER))'; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -955 THEN NULL; ELSE RAISE; END IF; END;

In the example above, the table (ADR3) is being created within schema TGTECC - the target snapshot.

If successful, the newly created tables display within Oracle DB for this schema = TGTECC

Note

Make sure to refresh the schema after running code build to make these new items visible.

Build the System Views

When the user attempts to Build System Views for the Project / Release / Subject Area within System Views, a Job Queue initiates to create these views within Oracle DB from the target system.

The Snapshot Datasource is the 'TGT' for the target system. In this case, TGTBAR is selected. The Schema in Oracle DB is the target snapshot schema or TGTBAR.

Click Build button to initiate the job queue to build out these Pass Thru Views within the Oracle database. Refer to section Migrate > System Views for details of this process.

The Job Queue is the same process using SQL SERVER for the database. This job runs a DROP of the view, and the second step runs a GRANT ACCESS within the TGT Datasource, and the third step runs a CREATE the view, so there are three times as many steps as there are tables for the Target Snapshot Schema.

Examples of this scripting are shown below:

DROP view

BEGIN

EXECUTE IMMEDIATE 'DROP VIEW COUNTRY_LIST_CUSTOM';

EXCEPTION WHEN OTHERS THEN NULL;

END;

Grant Select

GRANT SELECT ON COUNTRY_LIST TO WRKFOO2BAR, REPORT

Create View

CREATE VIEW WRKFOO2BAR.COUNTRY_LIST_CUSTOM AS SELECT * FROM TGTBAR.COUNTRY_LIST

Scripting for Datasets within Mapping

There is no change to the ETL - XML scripting. The Target Tables automatically generate within Oracle DB if the delivered Database Type Translations are correct from SQL Server to Oracle DB and within Oracle DB itself. These translations are delivered to work successfully and so there is no need to modify them.

Oracle uses Camel case or sentence case in field names, however, the field must be enclosed in quotes as “zDATA_READY" NUMBER(*,0) DEFAULT 1, 

An example of Oracle scripting for a Target Table is shown below:

CREATE TABLE DD_AD_T("zDATASOURCE" NVARCHAR2(50) not null, "ZDEPLOY" NVARCHAR2(10) not null,"zLegacyADDRNUMBER" NVARCHAR2(50) not null,"zLegacyDATE_FROM" NVARCHAR2(50) not null,"zLegacyNATION" NVARCHAR2(50) not null,CLIENT NVARCHAR2(3) null,ADDRNUMBER NVARCHAR2(10) null,DATE_FROM DATE null,NATION NVARCHAR2(1) null,TITLE NVARCHAR2(4) null,NAME1 NVARCHAR2(40) null,COUNTRY NVARCHAR2(3) null,LANGU VARCHAR2(1) null,REGION NVARCHAR2(3) null,"zERROR_LOG" NVARCHAR2(2000) null,"zLOADED" NUMBER(*,0) default 0 null,"zLOADDATE" DATE null,"zDATA_READY" NUMBER(*,0) default 1 null,"zSTAGE_READY" NUMBER(*,0) default 1 null,"zTARGET_READY" NUMBER(*,0) default 1 null,"zCHUNK" NUMBER(*,0) default 1 null,"zINTARGETSYS" NUMBER(*,0) default 0 null,"zREMEDIATED" NUMBER(*,0) default 0 null,"zCRITICAL" NUMBER(*,0) default 0 null)

Scripting for Sources within Mapping

There is no change to the ETL - XML scripting. The Source Table automatically generates within Oracle DB if the delivered Database Type Translations are correct from SQL Server to Oracle DB and within Oracle DB itself.

Once the mapping effort has been completed, Build the XML for the source. The View XML page shows minor coding changes for Oracle DB language.

An example of scripting for Oracle database is shown below:

CREATE TABLE WRKMM.ADDK_ADRC_ADVWRK_ADV_S( "AddressID" NUMBER(*,0) NOT NULL, "AddressLine1" NVARCHAR2(60), "AddressLine2" NVARCHAR2(60), "City" NVARCHAR2(30), "StateProvinceID" NUMBER(*,0), "PostalCode" NVARCHAR2(15), "SpatialLocation" VARCHAR(255), "rowguid" NVARCHAR2(50), "ModifiedDate" DATE, ZDATASOURCE NVARCHAR2(50), ZDEPLOY NVARCHAR2(10), ZLEGACYADDRNUMBER NVARCHAR2(50), ZLEGACYDATE_FROM NVARCHAR2(50), ZLEGACYNATION NVARCHAR2(50), "ZCLIENT" NVARCHAR2(3), "ZADDRNUMBER" NVARCHAR2(10), "ZDATE_FROM" NVARCHAR2(8), "ZNATION" NVARCHAR2(1), "ZNAME1" NVARCHAR2(40), "ZCITY1" NVARCHAR2(40), "ZDUPLICATE_SOURCE" NVARCHAR2(50), "ZDUPLICATE_OF" NVARCHAR2(50), "ZERROR_LOG" NVARCHAR2(2000), "ZINACTIVE_REASON" NVARCHAR2(2000), ZCRITICAL NVARCHAR2(255), ZACTIVE NVARCHAR2(255), CONSTRAINT PK_ADDK_ADRC_ADVWRK_ADV_S PRIMARY KEY ("AddressID"));

Creating Target Reports

The Reporting process in this scenario builds the views within the Working Database (Oracle DB) and the reporting data stores in REPORT db within SQL Server where Migration is run. Most of the standard reports auto generate and build views as expected. It is important to note that in the case of the Not IN reports, the Target system (ECC, S4H) Data Dictionary tables (DD tables) needs to be stored and refreshed within the Target Datasource so that these Check table reports may build the appropriate joins. Should any of the Not IN reports fail to build, the issue is with the DD tables of the Target Datasource. The user can manually correct these views for use.

The Counts and Recon reports build within the database.

ALL ERRORS Report

• Report is registered, but not created in the Database until the Report Refresh process.

• Report re-generates each time an Error report is changed (Added, Deleted, or Marked In-Active). NOTE: Marking In-Active requires the report Refreshed to initiate the Re-generate of All Errors Report.

Note

For any step that shows status of ERROR, click on the Edit  icon to view the error messaging. The Parameter of SQL provides a Value of the scripting of that report. The user should attempt to manually build the same report within Oracle to view the extended error messaging with assistance from Oracle for issues.

An example of the scripting for Target Reports in Oracle DB is shown below:

BEGIN EXECUTE IMMEDIATE 'CREATE VIEW WRKMM."ADDK_ADRC_T_ADDRNUMBER_IsNull" AS SELECT T.zDATASOURCE, T.ZDEPLOY, T.ZLEGACYADDRNUMBER, T.ZLEGACYDATE_FROM, T.ZLEGACYNATION, T.ADDRNUMBER FROM ADDK_ADRC_T T WHERE NVL(T.ADDRNUMBER,'' '') = '' '''; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -955 THEN NULL; ELSE RAISE; END IF; END;

Note

Due to the limitation to the length of field names, table names, report names in Oracle error messaging as shown below may display to show that the report could not be generated as expected.

Value Mapping Configuration for Oracle

The process allows users to configure the list of Check Tables for use in Value Mapping.

Check Tables are used to validate and control values entered to fields, primarily using SAP ETL. Within the Mapping exercise, target fields are assigned as a mapping type of XREF (Cross reference) and the assigned Check Table and its values are added to Value Mapping using table XTVALUEMAP within the Syniti Construct SQL Server database. The process of running the conversion within the ETL inserts to the MIGRATE.dbo.XTVALUEMAP table and update to the SRCCONSTRUCT.dbo.XTVALUEMAP table.

The team maps from source values to the check table target values in a Value Mapping exercise to produce valid translation. But, in some cases, the target values do not exist within the Target ETL. Or, the Target Check table does not yet exist within the Target ETL. This section provides the user with ability to create Check Tables and add fields within Check Tables for use in the Value Mapping process.

Once the Target ETL becomes synced with the values in the Check Table, these manually configured values change status to system configured values. Users may Insert, Update, Delete, or mark inactive the values in the Check Tables.

In the case where SQL Server is not the working database (ie: Oracle or HANA DB), a Snapshot Datasource must be built within the Migration SQL Server db for this process to work. Also, the local Snapshot must contain the required DD Tables including DD02L, DD02T, DD03L, DD04T, DD05P, and DD08L as well as any of the check tables used in Construct. Refer to section Administer > Value Mapping Config for details of this process and use.